#include "ExportExcel.h"
#include <QDir>

#include <QMessageBox>

/*
 * 函数名：ExportExcel
 * 函数说明:构造函数
 *
 *
 * 输入参数：parent： 父类指针
 * 输出参数：无
 * 返回值：无
 * */
ExportExcel::ExportExcel(QObject *parent) : QObject(parent)
{

}

/*
 * 函数名：exportStaffInfo
 * 函数说明:导出学生信息
 *
 *
 * 输入参数：fileName： 文件路径
 * 输出参数：无
 * 返回值：无
 * */

bool ExportExcel::exportStaffInfo(QString fileName)
{

    QAxObject *pApplication = NULL;
    QAxObject *pWorkBook = NULL;


    QAxObject * pSheet = sheet(fileName,&pApplication,&pWorkBook);



    if(!pApplication || !pWorkBook || !pSheet)
    {
        QMessageBox::warning(0,"错误","excel打开失败,请确认excel已经正确安装","确定");

        return false;
    }


    QList<StaffInfo> infos = m_stuService.staffList();

    QStringList labels;

    QStringList keys;


    labels<<"工号"<<"员工姓名"<<"性别";



    keys<<"staff_idcard"<<"staff_name"<<"staff_sex";


    for(int i = 0 ;i < labels.size();i++)
    {
        //写入数据

        QString& label = labels[i];

        QAxObject *pRange = pSheet->querySubObject("Cells(int,int)", 1, i + 1);

        pRange->dynamicCall("Value", label);

    }



    for(int i = 0; i< infos.size();i++)
    {


        StaffInfo & info = infos[i];


        for(int j =0 ; j< keys.size();j++)
        {
            QVariant data;

            if(j==0)
            {
               data = info.staff_idcard;
            }else if(j==1)
            {
               data = info.staff_name;
            }else if(j==2)
            {
               data = (info.staff_sex==0)?"男":"女";

            }




            QAxObject *pRange = pSheet->querySubObject("Cells(int,int)", i + 2, j + 1);

            pRange->dynamicCall("Value", data.toString());

        }


    }


    //保存


    pWorkBook->dynamicCall("SaveAs(const QString &)", QDir::toNativeSeparators(fileName));


    pApplication->dynamicCall("Quit()");

    delete pApplication;

    pApplication = NULL;

    return true;

}

/*
 * 函数名：sheet
 * 函数说明:初始化操作表格的句柄
 *
 *
 * 输入参数：fileName： 文件名称
 * 输出参数：pApplication_out：连接excel控件   pWorkbook_out：打开工作薄
 * 返回值：无
 * */
QAxObject *ExportExcel::sheet(QString fileName, QAxObject **pApplication_out, QAxObject **pWorkbook_out)
{

    QAxObject *pApplication = NULL;
    QAxObject *pWorkBooks = NULL;
    QAxObject *pWorkBook = NULL;
    QAxObject *pSheets = NULL;
    QAxObject *pSheet = NULL;


    pApplication = new QAxObject();
    if( pApplication->setControl("Excel.Application"))//连接Excel控件
    {

    }else{
       pApplication->setControl("Ket.Application");//连接wps控件
    }
//    pApplication->setControl("Excel.Application");
    pApplication->dynamicCall("SetVisible(bool)", false);//false不显示窗体
    pApplication->setProperty("DisplayAlerts", false);//不显示任何警告信息。
    pWorkBooks = pApplication->querySubObject("Workbooks");//获取工作薄集合
    QFile file(fileName);
    if (file.exists())
    {
        pWorkBook = pWorkBooks->querySubObject("Open(const QString &)", fileName);//打开已存在的工作薄
    }
    else
    {
        pWorkBooks->dynamicCall("Add");
        pWorkBook = pApplication->querySubObject("ActiveWorkBook");
    }
    pSheets = pWorkBook->querySubObject("Sheets");//获取工作表集合
    pSheet = pSheets->querySubObject("Item(int)", 1);

    if(pApplication_out)
    {
        *pApplication_out = pApplication;
    }

    if(pWorkbook_out)
    {
        *pWorkbook_out = pWorkBook;
    }

    return pSheet;
}
